package dao;

import entities.Employee;
import wrappers.*;
import exceptions.DatabaseException;
import java.sql.*;
import java.util.*;

/**
 *
 * @author Alejandro
 */
public class EmployeeDAO extends AbstractDAO {

    @Override
    protected PreparedStatement stringForInsert(Connection c, Object obj) throws DatabaseException {
        try {
            Employee e = (Employee) obj;
            PreparedStatement st = c.prepareStatement("INSERT INTO employees(name, surname, job, manager, hiredate, salary, department) VALUES (?, ?, ?, ?, TO_DATE(?, 'DD.MM.YYYY'), ?, ?)");
            st.setString(1, e.getName());
            st.setString(2, e.getSurname());
            st.setString(3, e.getJob());
            if (e.getManager() == 0) {
                st.setNull(4, java.sql.Types.INTEGER);
            } else {
                st.setInt(4, e.getManager());
            }
            st.setString(5, e.getDate());
            st.setInt(6, e.getSalary());
            st.setInt(7, e.getDepartment());
            return st;
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
    }

    @Override
    protected PreparedStatement stringForDeleteById(Connection c, int id) throws DatabaseException {
        try {
            PreparedStatement st = c.prepareStatement("DELETE FROM employees WHERE id=?");
            st.setInt(1, id);
            return st;
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
    }

    @Override
    protected PreparedStatement stringForUpdateById(Connection c, Object obj) throws DatabaseException {
        try {
            Employee e = (Employee) obj;
            PreparedStatement st = c.prepareStatement("UPDATE employees SET name=?, surname=?, job=?, manager=?, hiredate=TO_DATE(?, 'DD.MM.YYYY'), salary=?, department=? WHERE id=?");
            st.setString(1, e.getName());
            st.setString(2, e.getSurname());
            st.setString(3, e.getJob());
            if (e.getManager() == 0) {
                st.setNull(4, java.sql.Types.INTEGER);
            } else {
                st.setInt(4, e.getManager());
            }
            st.setString(5, e.getDate());
            st.setInt(6, e.getSalary());
            st.setInt(7, e.getDepartment());
            st.setInt(8, e.getId());
            return st;
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
    }

    @Override
    protected List<Object> processForSelectAll(Connection c) throws DatabaseException {
        List<Object> list = new LinkedList<Object>();
        try {
            PreparedStatement st = c.prepareStatement("SELECT e.id, e.name, e.surname, e.job, NVL(e.manager, 0) as manager, e.hiredate, e.salary, e.department, d.title, e2.name || ' ' || e2.surname as view_manager FROM employees e, departments d, employees e2 WHERE e.department=d.id AND ((e.manager IS NULL AND e.id=e2.id) OR e.manager=e2.id)");
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                Employee e = new Employee();
                e.setId(rs.getInt("id"));
                e.setName(rs.getString("name"));
                e.setSurname(rs.getString("surname"));
                e.setJob(rs.getString("job"));
                e.setManager(rs.getInt("manager"));
                e.setDate(rs.getString("hiredate").substring(0, 11));
                e.setSalary(rs.getInt("salary"));
                e.setDepartment(rs.getInt("department"));
                EmployeeWrap ew = new EmployeeWrap(e);
                ew.setViewDepartment(new ViewElement("department", rs.getString("title")));
                ew.setViewManager(new ViewElement("manager", (e.getManager() == 0) ? "No manager" : rs.getString("view_manager")));
                list.add((Object) ew);
            }
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
        return list;
    }

    @Override
    protected Object processForSelectId(Connection c, int id) throws DatabaseException {
        Object obj = new Object();
        try {
            PreparedStatement st = c.prepareStatement("SELECT * FROM employees WHERE id=?");
            st.setInt(1, id);
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                Employee e = new Employee();
                e.setId(rs.getInt("id"));
                e.setName(rs.getString("name"));
                e.setSurname(rs.getString("surname"));
                e.setJob(rs.getString("job"));
                e.setManager(rs.getInt("manager"));
                e.setDate(rs.getString("hiredate").substring(0, 11));
                e.setSalary(rs.getInt("salary"));
                e.setDepartment(rs.getInt("department"));
                obj = (Object) e;
                break;
            }
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
        return obj;
    }

    @Override
    public List<Object> findByNameOrSurname(String query) throws DatabaseException {
        query = (query + " ");
        List<String> query2 = new LinkedList<String>();
        int j = -2;
        for (int i = 0; i < query.length(); i++) {
            if (query.charAt(i) == ' ' || query.charAt(i) == ',' || query.charAt(i) == ';') {
                if (j == -2) {
                    j += 2;
                }
                if (!query.substring(j, i).equals(" ") && !query.substring(j, i).equals(";") && !query.substring(j, i).equals(",") && !query.substring(j, i).equals("")) {
                    query2.add(query.substring(j, i));
                }
                j = i + 1;
            }
        }
        try {
            List<Object> list = new LinkedList<Object>();
            List<Integer> listId = new LinkedList<Integer>();
            Connection connection = dbc.connect();
            for (int i = 0; i < query2.size(); i++) {
                Statement st = connection.createStatement();
                ResultSet rs = st.executeQuery("SELECT e.id, e.name, e.surname, e.job, NVL(e.manager, 0) as manager, e.hiredate, e.salary, e.department, d.title, e2.name || ' ' || e2.surname as view_manager FROM employees e, departments d, employees e2 WHERE e.department=d.id AND ((e.manager IS NULL AND e.id=e2.id) OR e.manager=e2.id) AND (UPPER(e.name) LIKE UPPER('%" + query2.get(i) + "%') OR UPPER(e.surname) LIKE UPPER('%" + query2.get(i) + "%'))");
                while (rs.next()) {
                    if (!listId.contains(rs.getInt("id"))) {
                        listId.add(rs.getInt("id"));
                        Employee e = new Employee();
                        e.setId(rs.getInt("id"));
                        e.setName(rs.getString("name"));
                        e.setSurname(rs.getString("surname"));
                        e.setJob(rs.getString("job"));
                        e.setManager(rs.getInt("manager"));
                        e.setDate(rs.getString("hiredate").substring(0, 11));
                        e.setSalary(rs.getInt("salary"));
                        e.setDepartment(rs.getInt("department"));
                        EmployeeWrap ew = new EmployeeWrap(e);
                        ew.setViewDepartment(new ViewElement("department", rs.getString("title")));
                        ew.setViewManager(new ViewElement("manager", (e.getManager() == 0) ? "No manager" : rs.getString("view_manager")));
                        list.add((Object) ew);
                    }
                }
            }
            connection.close();
            return list;
        } catch (SQLException ex) {
            throw new DatabaseException(ex);
        }
    }
}
